import pymysql
class Mysql_Addr(object):
    def __init__(self):
        try:
            self.db = pymysql.connect(host="localhost",user="root",password="123456",database="Bishe")
            self.cursor = self.db.cursor()
            print("连接成功")
        except:
            print("连接失败")
    def addOne(self,lon,lat,user,time):
        sql = "INSERT INTO addr (`lon`, `lat`, `user_id`, `time`) VALUES ('%s','%s','%s','%s')" % (lon,lat,user,time)
        print(sql)
        try:
            self.cursor.execute(sql)
            self.db.commit()
        except:
            self.db.rollback()
    def deleteByUser(self,id):
        sql = "delete from addr where user_id=" + str(id)
        print(sql)
        try:
            self.cursor.execute(sql)
            self.db.commit()
        except:
            self.db.rollback()
    def getAllAddr(self,id):
        sql = "select lon,lat from addr where user_id = '%s'" % (id)
        print(sql)
        self.cursor.execute(sql)
        results = self.cursor.fetchall()
        return results
    def selectAllAddr(self):
        sql = "select * from addr order by addr_id"
        self.cursor.execute(sql)
        results = self.cursor.fetchall()
        return results
    def changeAddr(self,id,userId,lon,lat,time):
        sql = "update addr set lon='%s',lat='%s',user_id='%s',time='%s'  where addr_id='%s' " % (lon, lat, userId,time,id)
        print(sql)
        try:
            self.cursor.execute(sql)
            self.db.commit()
        except:
            self.db.rollback()
        return "ok"
    def deleteAddrById(self,id):
        sql = "delete from addr where addr_id=" + str(id)
        print(sql)
        try:
            self.cursor.execute(sql)
            self.db.commit()
        except:
            self.db.rollback()
    def selectAddrByUserId(self,id):
        sql = "select * from addr where user_id = '%s'" % (id)
        print(sql)
        try:
            self.cursor.execute(sql)
            results = self.cursor.fetchall()
            return results
        except:
            return "error"
    def addNewAddr(self,id,lon,lat,time):
        sql = "INSERT INTO addr (`user_id`, `lon`, `lat`, `time`) VALUES ('%s','%s','%s','%s')" % (
            id,lon,lat,time)
        print(sql)
        try:
            self.cursor.execute(sql)
            self.db.commit()
        except:
            self.db.rollback()
    def __del__(self):
        self.db.close()